﻿using System;
using System.Collections.Generic;
using System.Data;

namespace Qzb.DBHelper
{
    public class SqlBuilder
    {
        private SqlBuilder()
        {
        }

        public static string CreateDeleteSql(string sourceTableName, string where)
            => $"DELETE FROM {sourceTableName} {where}";

        public static string CreateInsertSql(string sourceTableName, string[] sourceColumns, string sqlParamPlaceHolder)
        {
            var sqlParameterName = GetSqlParameterName(sourceColumns, sqlParamPlaceHolder);
            return $"INSERT INTO {sourceTableName}({string.Join(", ", sourceColumns)}) VALUES({string.Join(", ", sqlParameterName)}) ";
        }

        public static string CreateSelectSql(string sourceTableName, string[] sourceColumns, string where, List<SqlOrder> orders)
            => $"SELECT {string.Join(", ", sourceColumns)} FROM {sourceTableName} {where} {((orders.Count == 0) ? string.Empty : ("ORDER BY " + string.Join(", ", orders)))}";

        public static string CreateUpdateSql(string sourceTableName, string[] sourceColumns, string where, string sqlParamPlaceHolder)
        {
            var sqlParamName = GetSqlParameterName(sourceColumns, sqlParamPlaceHolder);
            var strArray2 = new string[sourceColumns.Length];

            for (var i = 0; i < sourceColumns.Length; i++)
            {
                strArray2[i] = sourceColumns[i] + " = " + sqlParamName[i];
            }
            return $"UPDATE {sourceTableName} SET {string.Join(", ", strArray2)} {where} ";
        }

        public static string CreateWhereClause(IDataParameter[] whereParams, string sqlParamPlaceHolder)
        {
            var paramsLength = whereParams.Length;
            if (paramsLength == 0)
                return string.Empty;

            var strArray = new string[paramsLength];
            for (var i = 0; i < paramsLength; i++)
            {
                if (whereParams[i].SourceVersion == DataRowVersion.Original)
                {
                    strArray[i] = whereParams[i].ParameterName.Substring("Original_".Length)
                        + " = "
                        + GetSqlParameterName(whereParams[i].ParameterName, sqlParamPlaceHolder);
                }
                else
                {
                    strArray[i] = whereParams[i].ParameterName
                        + " = "
                        + GetSqlParameterName(whereParams[i].ParameterName, sqlParamPlaceHolder);
                }
            }

            return ("WHERE " + string.Join(" AND ", strArray));
        }

        public static string GetFormatedSql(IDbCommand cmd, string sqlParamPlaceHolder)
        {
            if (cmd == null)
                return string.Empty;

            var commandText = cmd.CommandText;
            try
            {
                string[] strArray;
                int num;
                if ((cmd.Parameters == null) || (cmd.Parameters.Count == 0))
                    return commandText;

                if (cmd.CommandType == CommandType.Text)
                {
                    strArray = commandText.Split(sqlParamPlaceHolder.ToCharArray());
                    if (sqlParamPlaceHolder == "?")
                    {
                        for (num = 0; num < cmd.Parameters.Count; num++)
                        {
                            strArray[num + 1] = $"{GetFormatParamValue((IDataParameter)cmd.Parameters[num])}{strArray[num + 1]}";
                        }
                    }
                    else
                    {
                        num = 0;
                        while (num < cmd.Parameters.Count)
                        {
                            strArray[num + 1] = (sqlParamPlaceHolder + strArray[num + 1]).Replace(
                                sqlParamPlaceHolder + ((IDataParameter)cmd.Parameters[num]).ParameterName
                                , GetFormatParamValue((IDataParameter)cmd.Parameters[num]));
                            num++;
                        }
                    }
                    commandText = string.Join("", strArray);
                    var index = commandText.IndexOf(" where ");
                    if (index > 0)
                        commandText = commandText.Substring(0, index)
                            + commandText.Substring(index).Replace("= ''", " is null ");

                    return commandText;
                }

                strArray = new string[cmd.Parameters.Count];
                for (num = 0; num < cmd.Parameters.Count; num++)
                {
                    strArray[num] = $"'{Convert.ToString(((IDataParameter)cmd.Parameters[num]).Value)}'";
                }
                if (((IDataParameter)cmd.Parameters[0]).Direction == ParameterDirection.ReturnValue)
                {
                    commandText = $"{strArray[0]}={commandText}({string.Join(",", strArray, 1, strArray.Length - 1)})";
                }
                else
                {
                    commandText = $"{commandText}({string.Join(",", strArray)})";
                }
            }
            catch
            {
            }

            return commandText;
        }

        private static string GetFormatParamValue(IDataParameter parameter)
            => $"'{Convert.ToString(parameter.Value)}'";

        public static string GetSqlParameterName(string paramName, string sqlParamPlaceHolder)
        {
            if (sqlParamPlaceHolder == "?")
                return sqlParamPlaceHolder;

            return (sqlParamPlaceHolder + paramName);
        }

        public static string[] GetSqlParameterName(string[] sourceColumns, string sqlParamPlaceHolder)
        {
            int num;
            var strArray = new string[sourceColumns.Length];

            if (sqlParamPlaceHolder == "?")
            {
                for (num = 0; num < sourceColumns.Length; num++)
                {
                    strArray[num] = "?";
                }
            }
            else
            {
                for (num = 0; num < sourceColumns.Length; num++)
                {
                    strArray[num] = sqlParamPlaceHolder + sourceColumns[num];
                }
            }

            return strArray;
        }

        public static string GetTableNameFromSQL(string sql)
        {
            var index = sql.ToUpper().IndexOf(" FROM ");
            var str = sql.Substring(index + 6);
            index = str.IndexOf(" ");
            if (index >= 0)
            {
                str = str.Substring(0, index);
            }
            else
            {
                index = str.IndexOf(",");
                if (index >= 0)
                {
                    str = str.Substring(0, index);
                }
            }
            str = str.Trim();
            if (string.IsNullOrEmpty(str))
            {
                str = "Table";
            }
            return str;
        }
    }
}
